﻿using System.Collections.Generic;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Moq;
using Moq.Stub;
using Nvigorate.Data;
using Nvigorate.Relational.Binding;

namespace Nvigorate.Test.Data
{
    /// <summary>
    /// Summary description for QueryTableTests
    /// </summary>
    [TestClass]
    public class QueryTableTests : DataTestBase
    {
        #region GetInsertList tests

        [TestMethod]
        public void GetInsertList_AllColumnsIncluded_ReturnsValidSql()
        {
            // Arrange
            var tableName = "Employee";
            var provider = new Mock<IDatabaseSpecificProvider>();
            provider.ExpectGet(p => p.ParameterPrefix).Returns("X_");

            // Act
            var table = new QueryTable(provider.Object, tableName);
            table.AddColumn(new QueryColumn {ColumnName = "Id", UsedInQuery = true});
            table.AddColumn(new QueryColumn {ColumnName = "Name", UsedInQuery = true});
            var actual = table.GetInsertList();

            // Assert
            Assert.AreEqual("(Id, Name) VALUES (X_Id, X_Name)", actual);
        }

        [TestMethod]
        public void GetInsertList_SomeColumnsNotIncluded_ReturnsSqlWithOnlyIncludedColumns()
        {
            // Arrange
            var tableName = "Employee";
            var provider = new Mock<IDatabaseSpecificProvider>();
            provider.ExpectGet(p => p.ParameterPrefix).Returns("X_");

            // Act
            var table = new QueryTable(provider.Object, tableName);
            table.AddColumn(new QueryColumn {ColumnName = "Id", UsedInQuery = true});
            table.AddColumn(new QueryColumn {ColumnName = "Address", UsedInQuery = false});
            table.AddColumn(new QueryColumn {ColumnName = "Name", UsedInQuery = true});
            var actual = table.GetInsertList();

            // Assert
            Assert.AreEqual("(Id, Name) VALUES (X_Id, X_Name)", actual);
        }

        #endregion

        #region GetSelectList Tests

        [TestMethod]
        public void GetSelectList_AllColumnsIncluded_ReturnsValidSql()
        {
            // Arrange
            var tableName = "Employee";
            var provider = new Mock<IDatabaseSpecificProvider>();
            provider.ExpectGet(p => p.ParameterPrefix).Returns("X_");

            // Act
            var table = new QueryTable(provider.Object, tableName);
            table.AddColumn(new QueryColumn {ColumnName = "Id", ColumnAlias = "EmpId", UsedInQuery = true});
            table.AddColumn(new QueryColumn {ColumnName = "Name", ColumnAlias = "EmpName", UsedInQuery = true});
            var actual = table.GetSelectList();

            // Assert
            Assert.AreEqual("Employee.Id AS [EmpId], Employee.Name AS [EmpName]", actual);
        }

        [TestMethod]
        public void GetSelectList_NoAliasProvided_ReturnsValidSql()
        {
            // Arrange
            var tableName = "Employee";
            var provider = new Mock<IDatabaseSpecificProvider>();
            provider.ExpectGet(p => p.ParameterPrefix).Returns("X_");

            // Act
            var table = new QueryTable(provider.Object, tableName);
            table.AddColumn(new QueryColumn {ColumnName = "Id", UsedInQuery = true});
            table.AddColumn(new QueryColumn {ColumnName = "Name", UsedInQuery = true});
            var actual = table.GetSelectList();

            // Assert
            Assert.AreEqual("Employee.Id AS [Employee.Id], Employee.Name AS [Employee.Name]", actual);
        }

        [TestMethod]
        public void GetSelectList_SomeColumnsNotIncluded_ReturnsSqlWithOnlyIncludedColumns()
        {
            // Arrange
            var tableName = "Employee";
            var provider = new Mock<IDatabaseSpecificProvider>();
            provider.ExpectGet(p => p.ParameterPrefix).Returns("X_");

            // Act
            var table = new QueryTable(provider.Object, tableName);
            table.AddColumn(new QueryColumn {ColumnName = "Id", UsedInQuery = true});
            table.AddColumn(new QueryColumn {ColumnName = "Address", UsedInQuery = false});
            table.AddColumn(new QueryColumn {ColumnName = "Name", UsedInQuery = true});
            var actual = table.GetSelectList();

            // Assert
            Assert.AreEqual("Employee.Id AS [Employee.Id], Employee.Name AS [Employee.Name]", actual);
        }

        #endregion

        #region GetSelectJoin Tests

        [TestMethod]
        public void GetSelectJoin_SingleColumnRelation_ReturnsValidSql()
        {
            // Arrange
            var tableName = "EmployeeInfo";
            var provider = new Mock<IDatabaseSpecificProvider>();
            provider.ExpectGet(p => p.ParameterPrefix).Returns("X_");
            var relation = new Mock<IRelation>();
            relation.Stub(r => r.Fields, new Dictionary<string, string>
                                             {
                                                 {"EmployeeId", "Id"}
                                             });
            relation.Stub(r => r.Subject, "Employee");
            relation.Stub(r => r.Relative, tableName);

            // Act
            var table = new QueryTable(provider.Object, tableName);
            table.ParentRelation = relation.Object;
            var actual = table.GetSelectJoin();

            // Assert
            Assert.AreEqual("left join EmployeeInfo on Employee.EmployeeId = EmployeeInfo.Id", actual);
        }

        [TestMethod]
        public void GetSelectJoin_MultipleColumnRelation_ReturnsValidSql()
        {
            // Arrange
            var tableName = "EmployeeInfo";
            var provider = new Mock<IDatabaseSpecificProvider>();
            provider.ExpectGet(p => p.ParameterPrefix).Returns("X_");
            var relation = new Mock<IRelation>();
            relation.Stub(r => r.Fields, new Dictionary<string, string>
                                             {
                                                 {"EmployeeId", "Id"},
                                                 {"EmployeeName", "Name"}
                                             });

            relation.Stub(r => r.Subject, "Employee");
            relation.Stub(r => r.Relative, tableName);

            // Act
            var table = new QueryTable(provider.Object, tableName);
            table.ParentRelation = relation.Object;
            var actual = table.GetSelectJoin();

            // Assert
            Assert.AreEqual("left join EmployeeInfo on Employee.EmployeeId = EmployeeInfo.Id AND " +
                            "Employee.EmployeeName = EmployeeInfo.Name", actual);
        }

        #endregion

        #region GetUpdateList Tests

        [TestMethod]
        public void GetUpdateList_AllColumnsIncluded_ReturnsValidSql()
        {
            // Arrange
            var tableName = "EmployeeInfo";
            var provider = new Mock<IDatabaseSpecificProvider>();

            // Act
            var table = new QueryTable(provider.Object, tableName);
            table.AddColumn(new QueryColumn { ColumnName = "Id", UsedInQuery = true });
            table.AddColumn(new QueryColumn { ColumnName = "Name", UsedInQuery = true });
            var actual = table.GetUpdateList();

            // Assert
            Assert.AreEqual("Id = @Id,Name = @Name", actual);                        
        }

        [TestMethod]
        public void GetUpdateList_SomeColumnsNotIncluded_ReturnsValidSqlWithOnlyIncludedColumns()
        {
            // Arrange
            var tableName = "EmployeeInfo";
            var provider = new Mock<IDatabaseSpecificProvider>();

            // Act
            var table = new QueryTable(provider.Object, tableName);
            table.AddColumn(new QueryColumn { ColumnName = "Id", UsedInQuery = true });
            table.AddColumn(new QueryColumn {ColumnName = "Address", UsedInQuery = false});
            table.AddColumn(new QueryColumn { ColumnName = "Name", UsedInQuery = true });
            var actual = table.GetUpdateList();

            // Assert
            Assert.AreEqual("Id = @Id,Name = @Name", actual);                                   
        }

        #endregion

        #region GetKeyListing Tests

        [TestMethod]
        public void GetKeyListing_SingleKey_ReturnsValidSql()
        {
            // Arrange
            var tableName = "EmployeeInfo";
            var provider = new Mock<IDatabaseSpecificProvider>();

            // Act
            var table = new QueryTable(provider.Object, tableName);
            table.AddColumn(new QueryColumn { ColumnName = "Id", UsedInQuery = true, PrimaryKey = true});
            table.AddColumn(new QueryColumn { ColumnName = "Address", UsedInQuery = false });
            table.AddColumn(new QueryColumn { ColumnName = "Name", UsedInQuery = true });
            var actual = table.GetKeyListing(false);

            // Assert
            Assert.AreEqual("Id", actual);                                   
        }

        [TestMethod]
        public void GetKeyListing_CompositeKey_ReturnsValidSql()
        {
            // Arrange
            var tableName = "EmployeeInfo";
            var provider = new Mock<IDatabaseSpecificProvider>();

            // Act
            var table = new QueryTable(provider.Object, tableName);
            table.AddColumn(new QueryColumn { ColumnName = "Id", UsedInQuery = true, PrimaryKey = true});
            table.AddColumn(new QueryColumn { ColumnName = "Address", UsedInQuery = true });
            table.AddColumn(new QueryColumn { ColumnName = "Name", UsedInQuery = true, PrimaryKey = true});
            var actual = table.GetKeyListing(false);

            // Assert
            Assert.AreEqual("Id, Name", actual);                                   
        }

        [TestMethod]
        public void GetKeyListing_IncludePrefix_ReturnsValidSql()
        {
            // Arrange
            var tableName = "EmployeeInfo";
            var provider = new Mock<IDatabaseSpecificProvider>();

            // Act
            var table = new QueryTable(provider.Object, tableName);
            table.AddColumn(new QueryColumn { ColumnName = "Id", UsedInQuery = true, PrimaryKey = true });
            table.AddColumn(new QueryColumn { ColumnName = "Address", UsedInQuery = true });
            table.AddColumn(new QueryColumn { ColumnName = "Name", UsedInQuery = true, PrimaryKey = true });
            var actual = table.GetKeyListing(true);

            // Assert
            Assert.AreEqual("EmployeeInfo.Id, EmployeeInfo.Name", actual);                                   
        }

        #endregion

        #region GetKeyDefinitionList Tests

        [TestMethod]
        public void GetKeyDefinitionLists_SingleKey_ReturnsValidSql()
        {
            // Arrange
            var tableName = "EmployeeInfo";
            var provider = CreateProvider();

            // Act
            var table = new QueryTable(provider.Object, tableName);
            table.AddColumn(new QueryColumn { ColumnName = "Id", UsedInQuery = true, PrimaryKey = true, ColumnType = typeof(int)});
            table.AddColumn(new QueryColumn { ColumnName = "Address", UsedInQuery = false });
            table.AddColumn(new QueryColumn { ColumnName = "Name", UsedInQuery = true, ColumnType = typeof(string)});
            var actual = table.GetKeyDefinitionList();

            // Assert
            Assert.AreEqual("Id int", actual);                                   
            
        }

        [TestMethod]
        public void GetKeyDefinitions_CompositeKey_ReturnsValidSql()
        {
            // Arrange
            var tableName = "EmployeeInfo";
            var provider = CreateProvider();

            // Act
            var table = new QueryTable(provider.Object, tableName);
            table.AddColumn(new QueryColumn { ColumnName = "Id", UsedInQuery = true, PrimaryKey = true, ColumnType = typeof(int) });
            table.AddColumn(new QueryColumn { ColumnName = "Address", UsedInQuery = false });
            table.AddColumn(new QueryColumn { ColumnName = "Name", UsedInQuery = true, PrimaryKey = true, ColumnType = typeof(string) });
            var actual = table.GetKeyDefinitionList();

            // Assert
            Assert.AreEqual("Id int, Name varchar", actual);                                   
        }

        #endregion


    }
}